import { Sequelize, Options } from 'sequelize';
import dotenv from 'dotenv';
import path from 'path';
import { getLogger } from '@/utils/logger';

// 使用统一的日志工具，创建数据库专用的日志记录器
const logger = getLogger('database');

// 加载环境变量
const env = process.env.NODE_ENV || 'development';
const envFile = `.env.${env}`;
const defaultEnvFile = '.env';

// 先尝试加载指定环境的配置文件，如果不存在则加载默认.env文件
dotenv.config({ path: path.resolve(process.cwd(), envFile) });
dotenv.config({ path: path.resolve(process.cwd(), defaultEnvFile) });

// 获取当前环境
const currentEnv = process.env.NODE_ENV || 'development';

// 数据库配置
const config: {[key: string]: Options} = {
  // 开发环境配置
  development: {
    database: process.env.DB_NAME || 'ruoyi_node_dev',
    username: process.env.DB_USER || 'root',
    password: process.env.DB_PASSWORD || 'password',
    host: process.env.DB_HOST || 'localhost',
    port: parseInt(process.env.DB_PORT || '3306', 10),
    dialect: 'mysql',
    timezone: '+08:00',
    define: {
      charset: 'utf8mb4',
      collate: 'utf8mb4_unicode_ci',
      timestamps: true,
      underscored: true, // 将驼峰命名转换为下划线
      freezeTableName: true, // 不自动将表名变成复数
    },
    // 连接池配置 - 参考若依Druid配置
    pool: {
      max: parseInt(process.env.DB_POOL_MAX || '20', 10), // 最大连接数
      min: parseInt(process.env.DB_POOL_MIN || '10', 10), // 最小连接数
      acquire: parseInt(process.env.DB_ACQUIRE_TIMEOUT || '60000', 10), // 获取连接的超时时间 (毫秒)
      idle: parseInt(process.env.DB_IDLE_TIMEOUT || '30000', 10), // 连接空闲时间 (毫秒)
    },
    logging: (sql: string) => {
      // 自定义 SQL 日志输出，使用中文释义
      if (sql.includes('SELECT 1+1 AS result')) {
        logger.info('执行数据库连接测试');
      } else if (sql.includes('/* 查询数据库连接数 */')) {
        logger.info('查询数据库当前连接数');
      } else if (sql.includes('SHOW STATUS LIKE "Threads_connected"')) {
        logger.info('查询数据库当前连接数');
      } else if (sql.includes('SHOW STATUS')) {
        logger.info('查询数据库状态信息');
      } else {
        // logger.info(sql);
      }
    },
    benchmark: true, // 记录查询执行时间
    dialectOptions: {
      connectTimeout: parseInt(process.env.DB_CONNECTION_TIMEOUT || '30000', 10), // 连接超时时间
      dateStrings: true, // 日期字符串，而不是时间戳
      typeCast: true,
      decimalNumbers: true,
      supportBigNumbers: true,
      bigNumberStrings: true,
    }
  },
  
  // 测试环境配置
  test: {
    database: process.env.DB_NAME || 'ruoyi_node_test',
    username: process.env.DB_USER || 'root',
    password: process.env.DB_PASSWORD || 'password',
    host: process.env.DB_HOST || 'localhost',
    port: parseInt(process.env.DB_PORT || '3306', 10),
    dialect: 'mysql',
    timezone: '+08:00',
    define: {
      charset: 'utf8mb4',
      collate: 'utf8mb4_unicode_ci',
      timestamps: true,
      underscored: true,
      freezeTableName: true,
    },
    pool: {
      max: 10, // 测试环境连接数减少
      min: 5,
      acquire: parseInt(process.env.DB_ACQUIRE_TIMEOUT || '60000', 10),
      idle: parseInt(process.env.DB_IDLE_TIMEOUT || '30000', 10),
    },
    logging: false, // 测试环境关闭日志
    dialectOptions: {
      connectTimeout: parseInt(process.env.DB_CONNECTION_TIMEOUT || '30000', 10),
      dateStrings: true,
      typeCast: true,
      decimalNumbers: true,
    }
  },
  
  // 生产环境配置
  production: {
    database: process.env.DB_NAME || 'ruoyi_node_prod',
    username: process.env.DB_USER || 'root',
    password: process.env.DB_PASSWORD || 'password',
    host: process.env.DB_HOST || 'localhost',
    port: parseInt(process.env.DB_PORT || '3306', 10),
    dialect: 'mysql',
    timezone: '+08:00',
    define: {
      charset: 'utf8mb4',
      collate: 'utf8mb4_unicode_ci',
      timestamps: true,
      underscored: true,
      freezeTableName: true,
    },
    pool: {
      max: parseInt(process.env.DB_POOL_MAX || '50', 10), // 生产环境可以支持更高的并发
      min: parseInt(process.env.DB_POOL_MIN || '20', 10),
      acquire: parseInt(process.env.DB_ACQUIRE_TIMEOUT || '60000', 10),
      idle: parseInt(process.env.DB_IDLE_TIMEOUT || '30000', 10),
    },
    logging: (sql: string) => {
      // 自定义 SQL 日志输出，使用中文释义
      if (sql.includes('SELECT 1+1 AS result')) {
        logger.info('执行数据库连接测试');
      }
      // 添加对SHOW STATUS查询的处理
      else if (sql.includes('/* 查询数据库连接数 */')) {
        logger.info('查询数据库当前连接数');
      } 
      else if (sql.includes('SHOW STATUS LIKE "Threads_connected"')) {
        logger.info('查询数据库当前连接数');
      } 
      else if (sql.includes('SHOW STATUS')) {
        logger.info('查询数据库状态信息');
      }
      // 生产环境仅记录慢查询和连接测试
      else if (sql.includes('slow query')) {
        logger.warn(`检测到慢查询: ${sql}`);
      }
    },
    dialectOptions: {
      connectTimeout: parseInt(process.env.DB_CONNECTION_TIMEOUT || '30000', 10),
      dateStrings: true,
      typeCast: true,
      decimalNumbers: true,
      // 高可用配置
      supportBigNumbers: true,
      bigNumberStrings: true,
    }
  }
};

// 创建Sequelize实例
const sequelize = new Sequelize(config[currentEnv]);

// 测试数据库连接并处理错误
export const testConnection = async (): Promise<boolean> => {
  try {
    await sequelize.authenticate();
    // 这里不需要再输出日志，因为已经在 logging 函数中处理了
    return true;
  } catch (error) {
    logger.error(`数据库连接失败: ${error instanceof Error ? error.message : String(error)}`);
    return false;
  }
};

// 监听数据库连接错误 - 使用更安全的类型
process.on('unhandledRejection', (reason) => {
  logger.error(`未处理的Promise拒绝: ${reason instanceof Error ? reason.message : String(reason)}`);
});

// 导出Sequelize实例
export default sequelize; 